/*********************
*@file: db.go
*@author: jackwong
*@brief:  与数据库连接接口
*@function: 实现对数据的插入、删除等操作
*@version: 1.0
*@date: 2022/5/5 16:18
*********************/
package db

import (
	"database/sql"
	"fmt"
	"strconv"
	"time"

	"encoding/json"

	"groudserver/config"

	_ "github.com/go-sql-driver/mysql" //初始化
	"github.com/sirupsen/logrus"
)

// 定义结构体
type AlarmData struct {
	Id        uint64 `json:id`
	CarNum    int    `json:"carNum"`
	AlarmTime string `json:"alarmTime"`
}

type Auth struct {
	UserName string `json:"username"`
	Password string `json:"password"`
}
type UserInfo struct {
	Id         string `json:"id"`
	UserName   string `json:"username"`   //用户名
	Token      string `json:"token"`      //token
	CreateTime int64  `json:"createtime"` //创建时间
}

// 查询数据信息，返回Id、UserName、CreateTime //返回用户信息
type UserInfoEx struct {
	Id         int64  `json:"id"`         //用户ID
	UserName   string `json:"username"`   //用户名
	CreateTime int64  `json:"createtime"` //创建时间
}
type AlarmMsgEx struct {
	CarNum    int    `json:"carNum"`
	AlarmMsg  string `json:"alarmMsg"`
	AlarmTime string `json:"alarmTime"`
}

// 告警消息
type Alarm struct {
	Id        uint64 `json:id`  //附表id
	Mid       uint64 `json:mid` //主键
	CarNum    int    `json:"carNum"`
	AlarmTime string `json:"alarmTime"`
	AlarmMsg  string `json:"alarmMsg"`
}

// 分页查找,包含主表的信息
type AlarmEx struct {
	Id        uint64    `json:id`
	CarNum    int       `json:"carNum"`
	AlarmTime time.Time `json:"alarmTime"`
}

// 分页查找附表,附表的信息
type AlarmInfo struct {
	Id        uint64    `json:id`  //附表id
	Mid       uint64    `json:mid` //主键
	CarNum    int       `json:"carNum"`
	AlarmTime time.Time `json:"alarmTime"`
	AlarmMsg  string    `json:"alarmMsg"`
}

// 获取车辆列表
type CarList struct {
	CarNum int `json:"carNum"`
}

// 定义一个全局变量
var (
	db   *sql.DB
	Dbpv config.Cfg
)

// 数据库初始化
func InitDB() (err error) {
	//加载数据库配置
	JsonParse := config.NewJsonStruct()

	Dbpv = JsonParse.LoadCfg() //获取结构体信息
	database := Dbpv.DataBase.Username + ":" + Dbpv.DataBase.Password + Dbpv.DataBase.HostDB + "/" + Dbpv.DataBase.DBName
	dsn := database + "?charset=utf8mb4&parseTime=True"
	logrus.Info("dsn :", dsn)
	db, err = sql.Open("mysql", dsn)
	if err != nil {
		fmt.Print("db err:", err)
		return err
	}
	//与数据库连接
	Keepalive(db)
	return nil
}

// 插入主表信息
func InsertMtlDB(sql string, msg AlarmEx) bool {

	_, err := db.Exec(sql, msg.CarNum, msg.AlarmTime.Format("2006-01-02 15:04:05"))
	if err != nil {
		logrus.Error("insert maintbl failed", err)
		return false
	}
	return true
}

func InsertSubTblDB(sql string, msg AlarmInfo) bool {
	_, err := db.Exec(sql, msg.Mid, msg.CarNum, msg.AlarmTime.Format("2006-01-02 15:04:05"), msg.AlarmMsg)
	if err != nil {
		logrus.Error("insert maintbl failed", err)
		return false
	}
	return true
}

// 插入用户信息
func InsertUserInfo(msg UserInfo) (err error) {
	sqlStr := "insert into " + Dbpv.DataBase.UserInfoTbl + "(username, token, createtime)values(?,?,?)"
	_, err = db.Exec(sqlStr, msg.UserName, msg.Token, msg.CreateTime)
	if err != nil {
		logrus.Error("insert failed error", err)
		return err
	}
	return nil
}

// 数据库心跳保活检测
func Keepalive(db *sql.DB) {
	//开启心跳保活
	tick := time.NewTicker(5 * time.Second)
	go func(t *time.Ticker) {
		for {
			select {
			case <-t.C:
				err := db.Ping()
				if err != nil {
					fmt.Println("数据库连接失败，请重新连接")
				}
			}
		}
	}(tick)
}

// 删除数据库中的内容
func DeleteDB(strSql string) bool {
	stmt, err := db.Prepare(strSql)
	if err != nil {
		return false
	}
	defer stmt.Close()
	res, err := stmt.Exec()
	if err != nil {
		logrus.Error("Error del record:", err)
		return false
	}
	num, err := res.RowsAffected()
	if err != nil {
		logrus.Error("Error del record:", err)
		return false
	}
	logrus.Info("del success num:", num)
	return true
}

// 登录用户名、密码查询校验
func CheckUser(spl string) bool {
	query, _ := db.Query(spl)
	var flag string
	var count int
	for query.Next() {
		err := query.Scan(&flag)
		if err != nil {
			return false
		}
	}
	count, _ = strconv.Atoi(flag)
	if count > 0 {
		return true
	}

	return false
}

func CheckErr(err error) {
	if err != nil {
		panic(err)
	}
}

// 查询车辆数据
func GetCarList(sql string) ([]map[string]interface{}, bool) {
	rows, err := db.Query(sql)
	if err != nil {
		return nil, false
	}
	defer rows.Close()

	columns, err := rows.Columns()
	if err != nil {
		return nil, false
	}
	count := len(columns)
	tableData := make([]map[string]interface{}, 0)
	values := make([]interface{}, count)
	valuePtrs := make([]interface{}, count)
	for rows.Next() {
		for i := 0; i < count; i++ {
			valuePtrs[i] = &values[i]
		}
		rows.Scan(valuePtrs...)
		entry := make(map[string]interface{})
		for i, col := range columns {
			var v interface{}
			val := values[i]
			b, ok := val.([]byte)
			if ok {
				v = string(b)
			} else {
				v = val
			}
			entry[col] = v
		}
		tableData = append(tableData, entry)

	}
	return tableData, true
}

// 查询车辆列表
func GetCarLists(sql string) (string, error) {
	logrus.Info("获取车辆列表信息", sql)
	r, err := db.Query(sql)
	u := &CarList{}
	res := make([]*CarList, 0)
	defer r.Close()
	if err != nil {
		logrus.Errorf("err :%v", err)
	} else {
		for r.Next() {
			err = r.Scan(&u)
			if err != nil {
				logrus.Errorf("err :%v", err)
			} else {
				res = append(res, u)
			}

		}
	}
	jsonData, err := json.Marshal(res)
	if err != nil {
		return "", err
	}
	return string(jsonData), nil
}
func QueryToltalNumber(sql string) int {
	totalNum := 0
	rows, err := db.Query(sql)
	if err != nil {
		logrus.Info("Error get all", err)
		return 0
	}
	defer rows.Close()
	CheckErr(err)
	for rows.Next() {
		err := rows.Scan(&totalNum)
		CheckErr(err)
	}
	return totalNum
}

// 数据库查询,userinfo 信息
func QueryUserInfo(sql string) ([]*UserInfo, bool) {
	rows, err := db.Query(sql)
	if err != nil {
		logrus.Error("数据查询失败 QueryUserInfo query error", err)
		return nil, false
	}
	defer rows.Close()
	info := &UserInfo{}
	res := make([]*UserInfo, 0)
	for rows.Next() {
		err := rows.Scan(&info.UserName, &info.Token, &info.CreateTime)
		logrus.Info("查询数据用户 UserName: ", info.UserName, "\nToken:", info.Token, "\nCreateTime:", info.CreateTime)
		if err != nil {
			logrus.Error(err)
			return nil, false
		} else {
			res = append(res, info)
		}
	}
	return res, true
}

// 获取用户信息
func GetUserInfo(sql string) (*UserInfoEx, bool) {
	rows, err := db.Query(sql) //数据库查询信息
	if err != nil {
		logrus.Info("获取数据信息失败 GetUserInfo error", err)
		return nil, false
	}
	defer rows.Close()
	info := &UserInfoEx{}

	for rows.Next() {
		err := rows.Scan(&info.Id, &info.UserName, &info.CreateTime)
		logrus.Println("查询用户Id：", info.Id, "\n用户信息UserName:", info.UserName, "\nCreateTime:", info.CreateTime)
		if err != nil {
			logrus.Error(err)
			return nil, false
		} else {
			return info, true
		}
	}
	return info, false
}

// 告警主表信息查询
func QueryAlarmEx(sql string) ([]*AlarmData, bool) {
	res := make([]*AlarmData, 0)
	rows, err := db.Query(sql)
	if err != nil {
		logrus.Error("查询主表信息失败", err)
		return nil, false
	}
	defer rows.Close()
	for rows.Next() {
		tmp := &AlarmEx{}
		tmpData := &AlarmData{}
		err := rows.Scan(&tmp.Id, &tmp.CarNum, &tmp.AlarmTime)
		if err != nil {
			logrus.Error(err)
		} else {
			tmpData.Id = tmp.Id
			tmpData.CarNum = tmp.CarNum
			tmpData.AlarmTime = tmp.AlarmTime.Format("2006-01-02 15:04:05")
			res = append(res, tmpData)
		}
	}
	return res, true
}

// 查询附表信息
func QueryAlarmInfo(sql string) ([]*Alarm, bool) {
	res := make([]*Alarm, 0)
	rows, err := db.Query(sql)
	if err != nil {
		logrus.Error("查询主表信息失败", err)
		return nil, false
	}
	defer rows.Close()
	for rows.Next() {
		tmp := &AlarmInfo{}
		tempAlarm := &Alarm{}
		err := rows.Scan(&tmp.Id, &tmp.Mid, &tmp.CarNum, &tmp.AlarmTime, &tmp.AlarmMsg)
		if err != nil {
			logrus.Error(err)
		} else {
			tempAlarm.Id = tmp.Id
			tempAlarm.Mid = tmp.Mid
			tempAlarm.CarNum = tmp.CarNum
			tempAlarm.AlarmTime = tmp.AlarmTime.Format("2006-01-02 15:04:05")
			tempAlarm.AlarmMsg = tmp.AlarmMsg
			res = append(res, tempAlarm)
		}
	}
	return res, true
}
